Delete a MySQL database using Python and PyMySQL

Overview:

To remove a database from the MySQL Database Server the SQL command DROP DATABASE is used.
The DROP DATABASE command does the following:
- Deletes all the data present in the tables, deletes the schema of the tables
- Deletes the database
A database can be deleted from a Python Program using PyMySQL Python database client.

Remove a database using Python:

Inside a Python Program, obtain a connection object to the MySQL Database server.
Using a cursor object obtained through the connection, execute the SQL command DROP DATABASE specifying the name of the database to be deleted.
The example below removes an already existing database from a MySQL Server.
Since DROP DATABASE removes all the tables and their data this command has to be executed with caution.

Example:

# import the mysql client for python

import pymysql

# Create a connection object

dbIP = "127.0.0.1" # IP address of the MySQL database server

dbUserName = "root" # User name of the database server

dbUserPassword = "" # Password for the database user

databaseForDeletion = "D1" # Name of the database that is to be deleted

charSet = "utf8mb4" # Character set

cusrorType = pymysql.cursors.DictCursor

connection = pymysql.connect(host=dbIP, user=dbUserName, password=dbUserPassword,

charset=charSet,cursorclass=cusrorType)

try:

# Create a cursor object

dbCursor = connection.cursor()

# SQL Statement to delete a database

sql = "DROP DATABASE "+databaseForDeletion

# Execute the create database SQL statment through the cursor instance

dbCursor.execute(sql)

# SQL query string

sqlQuery = "SHOW DATABASES"

# Execute the sqlQuery

dbCursor.execute(sqlQuery)

#Fetch all the rows

databaseCollection = dbCursor.fetchall()

for datatbase in databaseCollection:

print(datatbase)

except Exception as e:

print("Exeception occured:{}".format(e))

finally:

connection.close()

Output:

{'Database': 'information_schema'}

{'Database': 'NewDatabase'}

{'Database': 'mysql'}

{'Database': 'performance_schema'}

{'Database': 'test'}

In the output above the database D1 is not listed as it has been deleted by the sample Python Program using PyMySQL. To create a blank database refer to the article: Create A MySQL Database Using Python And PyMySQL .